package com.service.imp;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpSession;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import com.common.uilt.ConfigUtil;
import com.common.uilt.DateUtil;
import com.common.uilt.SerialNum;
import com.dao.AssetsDao;
import com.dao.DeptDao;
import com.dao.ReceiptDao;
import com.dao.ReceiptDetailsDao;
import com.model.AssetsBean;
import com.model.DeptBean;
import com.model.ReceiptBean;
import com.model.ReceiptDetailsBean;
import com.model.UserBean;

@Service
@Transactional(rollbackFor = { RuntimeException.class }, propagation = Propagation.REQUIRED)
public class ReceiptService {
	@Autowired
	private JdbcTemplate jdbcTemplate;
	@Autowired
	private AssetsDao assetsDao;
	@Autowired
	private DeptDao deptDao;
	@Autowired
	private SerialNum serialNum;
	@Autowired
	private ReceiptDetailsDao receiptDetalisDao;
	@Autowired
	private ReceiptDao receiptDao;

	public String getReceiptNO(String type) {
		// 查询最后插入的单号
		String sql = "select t.no from tb_receipt t  where t.id =(select max(a.id) from tb_receipt a where a.no like '%"
				+ type + "%')";
		String no = null;
		try {
			no = this.jdbcTemplate.queryForObject(sql, String.class);

		} catch (EmptyResultDataAccessException e) {
			// TODO: handle exception
		}
		return serialNum.getMoveOrderNo(no, type);
	}

	/**
	 * 保存单据
	 * 
	 * @param receiptBean
	 *            单据实例
	 * @param receiptdetailslist
	 *            单据明细
	 * @param session
	 *            制单人
	 * @param string
	 *            单据类型
	 */
	public void sevaReceipt(ReceiptBean receiptBean,
			List<ReceiptDetailsBean> receiptdetailslist, HttpSession session,
			String string) {
		UserBean u = (UserBean) session.getAttribute("user");
		String account = u.getUser_account();
		receiptBean.setUser_id(account);
		receiptBean.setReceipt_type(this.serialNum.getMapkey().get(string));
		this.receiptDao.save(receiptBean);
		DeptBean dept=null;
		if(receiptBean.getDept_id()!=null&&!"".equals(receiptBean.getDept_id())){
			dept = this.deptDao.get(receiptBean.getDept_id());
			System.out.println("接收部门"+dept.getDept_name());
		}
		Double count_je = 0.00d;
		for (ReceiptDetailsBean rd : receiptdetailslist) {
			if (rd != null) {
				AssetsBean a = this.assetsDao.get(rd.getAssets_id());
				if(string.equals("CK")){
					a.setA_status("在用");
					if(dept!=null)
						rd.setUse_dept_name(dept.getDept_name());
				}
				else if(string.equals("TK")){
					a.setA_status("闲置");
					a.setA_use_dept(null);
				}
					
				else if(string.equals("BF"))
					a.setA_status("报废");
				else if(string.equals("DB"))
					a.setA_status("在用");
				if(dept!=null)
					a.setA_use_dept(dept);
				rd.setReceipt_id(receiptBean);
				System.out.println("原使用部门 or 盘点单据使用部门 or 出库单据使用部门"+rd.getUse_dept_name());
				count_je += Double.parseDouble(rd.getAssets_money());
				this.receiptDetalisDao.save(rd);
				this.assetsDao.update(a);
			}
		}
		receiptBean.setReceipt_money(count_je);
		this.receiptDao.update(receiptBean);
	}
	
	public void sevaRefundingReceipt(ReceiptBean receiptBean,
			List<ReceiptDetailsBean> receiptdetailslist, HttpSession session,
			String string) {
		// TODO Auto-generated method stub
		
	}
	/**
	 * 获取 一种单据列表
	 * @param type
	 * @return
	 */
	public List<Map<String, Object>> getReceiptByType(String type) {
		StringBuffer sb = new StringBuffer();
		sb.append("sqlect a.* from tb_receipt a where 1=1");
		if (type != null && !"".equals(type)) {
			sb.append(" and a.receipt_type='");
			sb.append(this.serialNum.getMapkey().get(type));
			sb.append("'");
		}
		return this.jdbcTemplate.queryForList(sb.toString());
	}
	/**
	 * 盘点
	 * @param receiptBean
	 * @param receiptdetailslist
	 * @param string
	 */
	public void inventory(ReceiptBean receiptBean,
			List<ReceiptDetailsBean> receiptdetailslist) {
		receiptBean.setReceipt_type(this.serialNum.getMapkey().get("PD"));
		this.receiptDao.save(receiptBean);
		Double count_je = 0.00d;
		for (ReceiptDetailsBean rd : receiptdetailslist) {
			if (rd != null) {
				rd.setReceipt_id(receiptBean);
				if(rd.getDifference()!=0){
					//有差异 进行盘盈或者盘亏
					if(rd.getDifference()>0){//盘盈
						overage(rd);
					}else if(rd.getDifference()<0){//盘亏
						shortage(rd);
					}
				}
				//单价× 实际数量
				count_je += Double.parseDouble(rd.getAssets_money())*rd.getReal_num();
				this.receiptDetalisDao.save(rd);
			}
		}
		receiptBean.setReceipt_money(count_je);
		this.receiptDao.update(receiptBean);
	}
	/**
	 * 盘盈
	 * @param rd
	 * @throws CloneNotSupportedException 
	 */
	public void overage(ReceiptDetailsBean rd){
		String head = ConfigUtil.getFileIo("serial.head", "serial.properties");
		String interval = ConfigUtil.getFileIo("serial.interval", "serial.properties");
		Long no = Long.parseLong(ConfigUtil.getFileIo("serial.no", "serial.properties"));
		
		StringBuffer hql =new StringBuffer();
		hql.append("from AssetsBean a where a.a_class_name=? and a.a_name=? and a.a_status=?");
		List<AssetsBean> list =new ArrayList<AssetsBean>();
		if(rd.getUse_dept_name()!=null&&!"".equals(rd.getUse_dept_name())){
			hql.append(" and a.a_use_dept=(select b.id from DeptBean b where b.dept_name=?)");
			list=this.assetsDao.find(hql.toString(),rd.getAssets_class_name()
					,rd.getAssets_name(),rd.getAssets_status(),rd.getUse_dept_name());
		}else{
			list=this.assetsDao.find(hql.toString(),rd.getAssets_class_name()
					,rd.getAssets_name(),rd.getAssets_status());
		}
		int i=0;
		for(i=0;i<Math.abs(rd.getDifference());i++){
			try {
				AssetsBean assetsBean =(AssetsBean) list.get(0).clone();
				assetsBean.setAssetsdetails_list(null);
				assetsBean.setA_number(head+interval+(no+i));
				assetsBean.setA_source("盘盈");
				assetsBean.setA_status("闲置");
				assetsBean.setA_register_data(DateUtil.SD2.format(new Date()));
				this.assetsDao.save(assetsBean);
			} catch (CloneNotSupportedException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		ConfigUtil.setprop("serial.no", (no+i)+"", "serial.properties","");
	}
	/**
	 * 盘亏
	 * @param rd
	 */
	public void shortage(ReceiptDetailsBean rd){
		StringBuffer hql =new StringBuffer();
		hql.append("from AssetsBean a where a.a_class_name=? and a.a_name=? and a.a_status=?");
		List<AssetsBean> list =new ArrayList<AssetsBean>();
		if(rd.getUse_dept_name()!=null&&!"".equals(rd.getUse_dept_name())){
			hql.append(" and a.a_use_dept=(select b.id from DeptBean b where b.dept_name=?)");
			list=this.assetsDao.find(hql.toString(),rd.getAssets_class_name()
						,rd.getAssets_name(),rd.getAssets_status(),rd.getUse_dept_name());
		}else{
			list=this.assetsDao.find(hql.toString(),rd.getAssets_class_name()
					,rd.getAssets_name(),rd.getAssets_status());
		}
		for(int i=0;i<Math.abs(rd.getDifference());i++){
			AssetsBean assetsBean =(AssetsBean) list.get(i);
			assetsBean.setAssetsdetails_list(null);
			assetsBean.setA_status("盘亏");
			this.assetsDao.update(assetsBean);
		
		}
	}
	/**
	 * 判断本月是否盘点
	 * @return true 本月没盘点 false 本月有盘点
	 */
	public boolean isMonthPD() {
		StringBuffer sql = new StringBuffer();
		//查询本月是否有盘点记录
		sql.append(" select count(*)");
		sql.append(" from tb_receipt a where");
		sql.append(" a.receipt_type='盘点单'");
		sql.append(" and to_char(to_date(a.receipt_data,'yyyy-mm-dd'),'yyyy-mm')=to_char(sysdate,'yyyy-mm')");
		//System.out.println(this.jdbcTemplate.queryForObject(sql.toString(), Integer.class));
		Integer count=new Integer(0);
		count=this.jdbcTemplate.queryForObject(sql.toString(),Integer.class);
		if(count==0) return true;
		return false;
	}
	/**
	 * 部门折旧报表
	 * @param data_month 查询时间 格式yyyy-MM
	 * @return 
	 */
	public List<Map<String,Object>> getreceiptdept_dform(String data_month){
		
		List<Map<String,Object>> formlist=new ArrayList<Map<String,Object>>();
		//Map<String,Object> nodemap=null;// new HashMap<String, Object>();
		//Integer maxleve = this.jdbcTemplate.queryForObject("select max(c.levelnum) from tb_dept c", Integer.class);
		//String formlistsql="select a.* from tb_dept a where a.id>=0 order by to_char(a.id)";
		StringBuffer formlistsql=new StringBuffer();
		formlistsql.append("select * from tb_dept c left join (select a.id as id_,");
		formlistsql.append(" count(b.id) as amount, nvl(sum(b.assets_value), 0) as total_price,");
		formlistsql.append(" nvl(sum(b.ad_ytd), 0) as yzd,nvl(sum(b.ad_count_d), 0) as count_d,nvl(sum(b.ad_jz), 0) jz");
		formlistsql.append(" from tb_dept a");
		formlistsql.append(" left join tb_assets_details b on a.id = b.dept_id ");
		formlistsql.append(" where 1=1 ");
		formlistsql.append(" and to_char(to_date(b.detalis_date,'yyyy-mm-dd'),'yyyy-mm')='").append(data_month).append("'");//to_char(to_date(");
		formlistsql.append(" group by a.id ) d on c.id=d.id_  where c.id>0 order by to_char(c.id)");
		formlist= this.jdbcTemplate.queryForList(formlistsql.toString());
		System.out.println(formlistsql);
		for (Map<String,Object> map:formlist) {
			if(map.get("isparent").toString().equals("1")){//如果是父节点
				map.putAll(getCountdeptByIsparent(map.get("id").toString(),data_month));
			}
		}
		return formlist;
	}
	public Map<String,Object> getCountdeptByIsparent(String pid,String data_month){
		StringBuffer sql= new StringBuffer();
		sql.append("select a.dept_name,");
		sql.append(" count(b.id) as 数量,");
		sql.append("nvl(sum(b.assets_value),0) as 价格,");
		sql.append("nvl(sum(b.ad_ytd),0) as 月折旧额,");
		sql.append("nvl(sum(b.ad_count_d),0) as 累计,");
		sql.append("nvl(sum(b.ad_jz),0) 净值,");
		sql.append("a.id,a.isparent,a.dept_pid ");
		sql.append(" from tb_dept a left join tb_assets_details b on a.id = b.dept_id");
		sql.append(" where a.dept_pid=");
		sql.append(pid);
		sql.append(" and (to_char(to_date(b.detalis_date,'yyyy-mm-dd'),'yyyy-mm')='").append(data_month).append("'");
		sql.append(" or b.detalis_date is null)");
		sql.append(" group by a.dept_name,a.id,a.isparent,a.dept_pid");
		
		List<Map<String,Object>> temp=this.jdbcTemplate.queryForList(sql.toString());
		BigDecimal amount=new BigDecimal(0);
		BigDecimal total_price=new BigDecimal(0);
		BigDecimal yzd=new BigDecimal(0);
		BigDecimal count_d=new BigDecimal(0);
		BigDecimal jz=new BigDecimal(0);
		Map<String,Object> m= new HashMap<String, Object>();
		for(Map<String,Object> map:temp){
			if(map.get("isparent").toString().equals("1")){//如果是父节点
				map.putAll(getCountdeptByIsparent(map.get("id").toString(),data_month));
			}
			//System.out.println(map.get("层级"));
			amount=amount.add((BigDecimal) map.get("数量"));
			total_price=total_price.add((BigDecimal) map.get("价格"));
			yzd=yzd.add((BigDecimal) map.get("月折旧额"));
			count_d=count_d.add((BigDecimal) map.get("累计"));
			jz=jz.add((BigDecimal) map.get("净值"));
		}
		
		m.put("amount", amount);
		m.put("total_price", total_price);
		m.put("yzd", yzd);
		m.put("count_d", count_d);
		m.put("jz",jz);
		
		return m;
	}

	public List<Map<String,Object>> getreceiptcalss_dform(String data_month) {
		List<Map<String,Object>> formlist=new ArrayList<Map<String,Object>>();
		
		StringBuffer formlistsql=new StringBuffer();
		formlistsql.append("select * from tb_assets_class c left join (select a.ac_id as ac_id_,");
		formlistsql.append(" count(b.id) as amount,nvl(sum(b.assets_value), 0) as total_price, nvl(sum(b.ad_ytd), 0) as yzd,");
		formlistsql.append(" nvl(sum(b.ad_count_d),0) as count_d, nvl(sum(b.ad_jz),0) jz");
		formlistsql.append(" from tb_assets_class a left join tb_assets_details b on a.ac_name = b.assets_class");
		formlistsql.append(" where 1=1");
		formlistsql.append(" and to_char(to_date(b.detalis_date,'yyyy-mm-dd'),'yyyy-mm')='").append(data_month).append("'");//to_char(to_date(");
		formlistsql.append(" group by a.ac_id) d on c.ac_id=d.ac_id_  where c.ac_id>0 order by to_char(c.ac_id)");
		formlist= this.jdbcTemplate.queryForList(formlistsql.toString());
		System.out.println(formlistsql.toString());
		for (Map<String,Object> map:formlist) {
			if(map.get("isparent").toString().equals("1")){//如果是父节点
				map.putAll(getCountclassByIsparent(map.get("ac_id").toString(),data_month));
			}
		}
		return formlist;
	}
	
	
	public Map<String, Object> getCountclassByIsparent(
			String pid, String data_month) {
		StringBuffer sql= new StringBuffer();
		sql.append("select a.ac_name,");
		sql.append(" count(b.id) as 数量,");
		sql.append("nvl(sum(b.assets_value),0) as 价格,");
		sql.append("nvl(sum(b.ad_ytd),0) as 月折旧额,");
		sql.append("nvl(sum(b.ad_count_d),0) as 累计,");
		sql.append("nvl(sum(b.ad_jz),0) 净值,");
		sql.append("a.ac_id,a.isparent,a.ac_pid from tb_assets_class a");
		sql.append(" left join tb_assets_details b on a.ac_name = b.assets_class");
		sql.append(" where a.ac_pid=");
		sql.append(pid);
		sql.append(" and (to_char(to_date(b.detalis_date,'yyyy-mm-dd'),'yyyy-mm')='").append(data_month).append("'");
		sql.append(" or b.detalis_date is null)");
		sql.append(" group by a.ac_name,a.ac_id,a.isparent,a.ac_pid");
		
		List<Map<String,Object>> temp=this.jdbcTemplate.queryForList(sql.toString());
		BigDecimal amount=new BigDecimal(0);
		BigDecimal total_price=new BigDecimal(0);
		BigDecimal yzd=new BigDecimal(0);
		BigDecimal count_d=new BigDecimal(0);
		BigDecimal jz=new BigDecimal(0);
		Map<String,Object> m= new HashMap<String, Object>();
		for(Map<String,Object> map:temp){
			if(map.get("isparent").toString().equals("1")){//如果是父节点
				map.putAll(getCountdeptByIsparent(map.get("id").toString(),data_month));
			}
			//System.out.println(map.get("层级"));
			amount=amount.add((BigDecimal) map.get("数量"));
			total_price=total_price.add((BigDecimal) map.get("价格"));
			yzd=yzd.add((BigDecimal) map.get("月折旧额"));
			count_d=count_d.add((BigDecimal) map.get("累计"));
			jz=jz.add((BigDecimal) map.get("净值"));
		}
		
		m.put("amount", amount);
		m.put("total_price", total_price);
		m.put("yzd", yzd);
		m.put("count_d", count_d);
		m.put("jz",jz);
		
		return m;
	}

	@SuppressWarnings("resource")
	public static void main(String[] args) throws Exception {
		ApplicationContext context = new ClassPathXmlApplicationContext(
				"applicationContext.xml");
		ReceiptService receiptService = (ReceiptService) context.getBean("receiptService");
		//System.out.println(receiptService.getCountByIsparent("50"));
		long startTime = System.currentTimeMillis();
		//receiptService.getreceiptdept_dform();

//		for(Map<String,Object> m:receiptService.getreceiptdept_dform("2015-05")){
//			System.out.println(m);
//		}
		receiptService.getReceiptdeptThreeForm("2015-06");

		long endTime = System.currentTimeMillis(); 
		System.out.println("time: " + (endTime - startTime)); 
		//receiptService.temp();
	}

	public List<Map<String,Object>> getReceiptdeptThreeForm(String data_month) throws Exception {
		String perDate=DateUtil.SD3.format(DateUtil.getNext(DateUtil.SD3.parse(data_month),Calendar.MONTH , -1));
		
		StringBuffer sql= new StringBuffer();
		sql.append("SELECT c.dept_name,nvl(c.early_number,0) as early_number,nvl(c.early_money,0) as early_money,");
		sql.append("                   nvl(d.add_number,0) as add_number,nvl(d.add_money,0) as add_money,");
		sql.append("                   nvl(g.dec_number,0) as dec_number,nvl(g.dec_money,0) as dec_money,");
		sql.append("                   nvl(j.end_number,0) as end_number,nvl(j.end_money,0) as end_money ");
		sql.append("FROM ( SELECT dept.dept_name,nvl(c_temp.数量,0) AS early_number,nvl(c_temp.金额,0) AS early_money FROM tb_dept dept");
		sql.append(" LEFT JOIN");
		sql.append(" ( SELECT b.use_dept_name, nvl(sum(b.real_num),0) AS 数量,nvl(sum(to_number(b.assets_money)*b.real_num),0) AS 金额  FROM tb_receipt a");
		sql.append(" LEFT JOIN tb_receipt_details b ON a.id=b.receipt_id WHERE a.receipt_type='盘点单'");
		sql.append(" AND b.assets_status='在用' AND to_char(to_date(a.receipt_data,'yyyy-mm-dd'),'yyyy-mm')='").append(perDate).append("'");
		sql.append(" GROUP BY b.use_dept_name) c_temp ON c_temp.use_dept_name=dept.dept_name");//--上月结余
		sql.append(" WHERE dept.id>0 and dept.isparent=0) c");
		sql.append(" LEFT JOIN");//查出增加
		sql.append(" (SELECT outdd.use_dept_name,nvl(sum(outdd.add_number),0) AS add_number,nvl(sum(outdd.add_money),0) AS add_money");
							//
		sql.append("  FROM (SELECT (SELECT aa_temp.dept_name FROM tb_dept aa_temp WHERE aa_temp.id=e.dept_id) AS use_dept_name,");
		sql.append("                nvl(sum(f.assets_num),0) AS add_number,nvl(sum(to_number(f.assets_money)*f.assets_num),0) AS add_money");
		sql.append("        FROM tb_receipt e");
		sql.append("  LEFT JOIN tb_receipt_details f ON e.id=f.receipt_id");
		sql.append("  WHERE e.receipt_type in('出库单') AND to_char(to_date(e.receipt_data,'yyyy-mm-dd'),'yyyy-mm')='").append(data_month).append("'");//本月出库
		sql.append("  GROUP BY e.dept_id");
		sql.append("   UNION ALL SELECT (SELECT aa_temp.dept_name FROM tb_dept aa_temp WHERE aa_temp.id=e.dept_id) AS use_dept_name,");
		sql.append("                        nvl(count(f.assets_number),0) AS add_number,");
		sql.append("                        nvl(sum(to_number(f.assets_money)),0) AS add_money");
		sql.append("             FROM tb_receipt e");
		sql.append("             LEFT JOIN tb_receipt_details f ON e.id=f.receipt_id");
		sql.append("             WHERE e.receipt_type in('调拨单') AND to_char(to_date(e.receipt_data,'yyyy-mm-dd'),'yyyy-mm')='").append(data_month).append("'");//本月调拨接收
		sql.append("             GROUP BY e.dept_id");
		sql.append("  UNION ALL  SELECT b.use_dept_name,nvl(sum(b.difference),0) AS add_number, nvl(sum(to_number(b.assets_money)*b.difference),0) AS add_money");
		sql.append("             FROM tb_receipt a LEFT JOIN tb_receipt_details b ON a.id=b.receipt_id ");//--查出本月盘点 盘盈的
		sql.append("             WHERE a.receipt_type='盘点单' AND b.assets_status='在用' AND to_char(to_date(a.receipt_data,'yyyy-mm-dd'),'yyyy-mm')='").append(data_month).append("'");
		sql.append("              AND b.difference>0");
		sql.append("  GROUP BY b.use_dept_name ) outdd");
		sql.append(" GROUP BY outdd.use_dept_name) d ON c.dept_name=d.use_dept_name   ");
		sql.append("LEFT JOIN");//查处减少
		sql.append(" (SELECT ttdd.use_dept_name,nvl(sum(ttdd.dec_number),0) AS dec_number, nvl(sum(ttdd.dec_money),0) AS dec_money");
		sql.append("  FROM  (SELECT i.use_dept_name, nvl(count(i.assets_number),0) AS dec_number,nvl(sum(to_number(i.assets_money)),0) AS dec_money");
		sql.append("          FROM tb_receipt h LEFT JOIN tb_receipt_details i ON h.id=i.receipt_id  WHERE h.receipt_type in('退库单')");
		sql.append("          AND to_char(to_date(h.receipt_data,'yyyy-mm-dd'),'yyyy-mm')='").append(data_month).append("'");//--查处本月退库
		sql.append("         GROUP BY i.use_dept_name");
		sql.append("        UNION ALL SELECT f.use_dept_name, nvl(count(f.assets_number),0) AS dec_number,nvl(sum(to_number(f.assets_money)),0) AS dec_money");
		sql.append("                   FROM tb_receipt e LEFT JOIN tb_receipt_details f ON e.id=f.receipt_id WHERE e.receipt_type in('调拨单')");
		sql.append("                   AND to_char(to_date(e.receipt_data,'yyyy-mm-dd'),'yyyy-mm')='").append(data_month).append("'");//--本月调拨减少
		sql.append("                   GROUP BY f.use_dept_name");
		sql.append("         UNION ALL  SELECT b.use_dept_name, nvl(sum(b.difference)*-1,0) AS dec_number,nvl(sum(to_number(b.assets_money)*b.difference*-1),0) AS dec_money");
		sql.append("                    FROM tb_receipt a  LEFT JOIN tb_receipt_details b ON a.id=b.receipt_id");//--查出本月盘点 盘亏的
		sql.append("                    WHERE a.receipt_type='盘点单' AND b.assets_status='在用' AND b.difference<0");
		sql.append("                           AND to_char(to_date(a.receipt_data,'yyyy-mm-dd'),'yyyy-mm')='").append(data_month).append("'");
		sql.append("                    GROUP BY b.use_dept_name) ttdd");
		sql.append("  GROUP BY ttdd.use_dept_name) g ON c.dept_name=g.use_dept_name ");
		sql.append(" LEFT JOIN"); //查处本月结余
		if(isMonthPD()){//--月末 如果没盘点
			sql.append(" (select k.dept_name,nvl(count(l.id),0) as end_number,nvl(sum(l.a_unit_price),0) as end_money");
			sql.append("   from tb_dept k left join tb_assets l on k.id=l.a_use_dept ");
			sql.append("  where k.id>0 group by k.dept_name) j on j.dept_name=c.dept_name ");

		}else{
			sql.append(" ( SELECT l.use_dept_name, nvl(sum(l.real_num),0) AS end_number,nvl(sum(to_number(l.assets_money)*l.real_num),0) AS end_money");
			sql.append("  FROM tb_receipt k LEFT JOIN tb_receipt_details l ON k.id = l.receipt_id WHERE k.receipt_type='盘点单' AND l.assets_status='在用'");
			sql.append("  AND to_char(to_date(k.receipt_data,'yyyy-mm-dd'),'yyyy-mm')='").append(data_month).append("'");//--本月盘点                                  
			sql.append(" GROUP BY l.use_dept_name) j ON j.use_dept_name=c.dept_name");
		}
		
		
		
		
		
		System.out.println(sql.toString());
		List<Map<String,Object>> list =this.jdbcTemplate.queryForList(sql.toString());
		return list;
	}

	
	
}
